package net.sf.rose.jdbc.query;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import net.sf.rose.jdbc.bean.DbUserBean;
import net.sf.rose.jdbc.service.Service;
import net.sf.rose.util.RoseRuntimeException;

/**
 * 类说明：自动拼接SQL语句
 * 
 * @author fengjian
 * @since 2013/05/18
 */
public class SimpleSQL extends QuerySQL {

	public SimpleSQL() {
		super();
	}

	public SimpleSQL(Service service) {
		super();
		super.service = service;
	}

	/**
	 * 功能:返回数据库操作的执行者
	 * 
	 * @return 数据库操作的执行者
	 */
	public DbUserBean getUser() {
		return this.service.getUser();
	}

	/**
	 * 功能:拼装SQL查询语句
	 * 
	 * @param map - 参数键值列表
	 */
	public void createSql(Map<String, Object> map) {
		this.getTableName(map);
		StringBuilder buf = super.setSQL("SELECT * FROM ");
		buf.append(this.tableName);
		// 创建查询条件SQL语句
		String condition = this._createConditionSql(map);
		if (!condition.equals("")) {
			buf.append(" WHERE ");
			buf.append(condition);
		}
		super.sql = buf;
	}

	/**
	 * 功能:拼装SQL查询语句
	 * 
	 * @param sql - 指定SQL语句
	 * @param map - 参数键值列表
	 */
	public void createSql(String sql, Map<String, Object> map) {
		this.getTableName(map);
		StringBuilder buf = super.setSQL(sql);
		// 创建查询条件SQL语句
		String condition = this._createConditionSql(map);
		if (!condition.equals("")) {
			buf.append(" WHERE ");
			buf.append(condition);
		}
		super.sql = buf;
	}

	/**
	 * 功能:拼装SQL查询语句
	 * 
	 * @param tableName - 表名
	 * @param key       - 字段名
	 * @param val       - 字段值
	 */
	public void createSqlByKeyAndValue(String tableName, String key, String val) {
		StringBuilder sb = super.setSQL("SELECT * FROM ");
		sb.append(tableName);
		sb.append(" WHERE ").append(key);
		if (val == null) {
			sb.append(" is ?");
		} else {
			sb.append("=?");
		}
		super.addParameter(val);
		super.sql = sb;
	}

	/**
	 * 功能:拼装SQL查询列表语句
	 * 
	 * @param sql  - SQL语句
	 * @param data - 参数对象
	 *             <p>
	 *             3.示例：表名：user
	 *             <p>
	 *             字段：userName 字符串；age 数字；birthday 日期；
	 *             <p>
	 *             String sql = "SELECT * FROM user where userName=#userName#";
	 *             <p>
	 *             Map<String, Object> data = new HashMap<>();
	 *             <p>
	 *             data.put("userName", "pingping");
	 *             <p>
	 *             MapDAO dao = new MapDAO(service);
	 *             <p>
	 *             MapSQL query = dao.getQuerySQL();
	 *             <p>
	 *             query.createSql(sql, data);
	 *             <p>
	 *             结果sql输出：select * from user where userName=?
	 *             <p>
	 *             结果查询参数："pingping"
	 */
	public void createSqlByMap(String sql, Map<String, Object> data) {
		String key = null;
		StringBuilder sb = super.setSQL(sql);
		int i = sb.indexOf("#");
		if (i == -1) {
			return;
		}
		int j = sb.indexOf("#", i + 1);
		do {
			key = sb.substring(i + 1, j);
			list.add(data.get(key));
			sb.replace(i, j + 1, "?");
			i = sb.indexOf("#");
			j = sb.indexOf("#", i + 1);
		} while (i != -1 && j != -1);
	}

	/**
	 * 功能:拼装SQL插入语句
	 * 
	 * @param data - 参数对象
	 */
	public void createInsertSql(Map<String, Object> data) {
		list.clear();
		StringBuilder sb = new StringBuilder();
		StringBuilder vb = new StringBuilder();
		this.getTableName(data);
		sb.append("INSERT ").append(this.tableName).append("(");
		vb.append("VALUES(");
		for (String key : data.keySet()) {
			sb.append(key).append(',');
			vb.append("?,");
			list.add(data.get(key));
		}
		sb.replace(sb.length() - 1, sb.length(), ")");
		vb.replace(vb.length() - 1, vb.length(), ")");
		sb.append(vb);
		super.sql = sb;
	}

	/**
	 * 功能:拼装批处理插入SQL语句
	 * 参数：beans - 类对象列表
	 */
	public void createBatchInsertSql(String tableName, List<Map<String, Object>> list) {
		StringBuilder sb = new StringBuilder();
		StringBuilder st = new StringBuilder();
		sb.append("insert into ").append(tableName).append('(');
		st.append("values(");
		List<String> keys = new ArrayList<>();
		Map<String, Object> map = list.get(0);
		for (String key : map.keySet()) {
			sb.append(key).append(',');
			st.append("?,");
			keys.add(key);
		}
		sb.deleteCharAt(sb.length() - 1);
		sb.append(')');
		st.deleteCharAt(st.length() - 1);
		st.append(')');
		sb.append(st.toString());
		super.setSQL(sb.toString());
		// 准备参数列表
		int size = keys.size();
		for (int k = 0; k < list.size(); k++) {
			Object[] arr = new Object[size];
			map = list.get(k);
			for (int i = 0; i < size; i++) {
				arr[i] = map.get(keys.get(i));
			}
			super.list.add(arr);
		}
	}

	/**
	 * 功能:拼装批处理插入SQL语句
	 * 参数：beans - 类对象列表
	 */
	public void createBatchUpdateSql(String tableName, String keyName, List<Map<String, Object>> list) {
		StringBuilder sb = new StringBuilder();
		sb.append("UPDATE ");
		sb.append(tableName);
		sb.append(" SET ");
		Map<String, Object> map = list.get(0);
		List<String> keys = new ArrayList<>();
		for (String key : map.keySet()) {
			sb.append(key).append("=?,");
			keys.add(key);
		}
		sb.deleteCharAt(sb.length() - 1);
		sb.append(" where ").append(keyName).append("=? ");
		super.setSQL(sb.toString());
		// 准备参数列表
		int size = map.size();
		for (int k = 0; k < list.size(); k++) {
			Object[] arr = new Object[size + 1];
			map = list.get(k);
			for (int i = 0; i < size; i++) {
				arr[i] = map.get(keys.get(i));
			}
			arr[size] = map.get(keyName);
			super.list.add(arr);
		}
	}

	/**
	 * 功能:拼装SQL更新语句
	 * 
	 * @param data      - 参数容器
	 * @param condition - 条件参数
	 */
	public void createUpdateSql(Map<String, Object> data, Map<String, Object> condition) {
		// 检查要操作的数据库表名是否已经指定
		this.getTableName(data);
		StringBuilder buf = new StringBuilder();
		buf.append("UPDATE ");
		buf.append(this.tableName);
		buf.append(" SET ");

		list.clear();
		for (String key : data.keySet()) {
			Object val = data.get(key);
			if (val != null) {
				char c = 0;
				String s = val.toString();
				if (s.length() > 0) {
					c = s.charAt(0);
				}
				// 如果首字母是+或-符号，则拼成自增长或自减少SQL文
				if (c == '+' || c == '-') {
					buf.append(key).append('=').append(key).append(val).append(", ");
				} else {
					buf.append(key).append("=?, ");
					list.add(val);
				}
			}
		}
		// 拼接SQL条件语句
		buf.deleteCharAt(buf.length() - 2);
		String con = this._createConditionSql(condition);
		if (!con.equals("")) {
			buf.append(" WHERE ");
			buf.append(con);
		}
		super.sql = buf;
	}

	/**
	 * 功能:拼装SQL更新语句
	 * 
	 * @param data      参数容器
	 * @param tableName 表名称
	 * @param key       键名
	 * @param value     键值
	 */
	public void createUpdateSql(Map<String, Object> data, String tableName, String key, String value) {
		this.getTableName(data);
		StringBuilder buf = new StringBuilder();
		buf.append("UPDATE ");
		buf.append(this.tableName);
		buf.append(" SET ");
		list.clear();
		for (String str : data.keySet()) {
			Object val = data.remove(str);
			if (val != null) {
				char c = 0;
				String s = val.toString();
				if (s.length() > 0) {
					c = s.charAt(0);
				}
				// 如果首字母是+或-符号，则拼成自增长或自减少SQL文
				if (c == '+' || c == '-') {
					buf.append(str).append('=').append(str).append(val).append(", ");
				} else {
					buf.append(str).append("=?, ");
					list.add(val);
				}
			}
		}
		// 拼接SQL条件语句
		buf.deleteCharAt(buf.length() - 2);
		buf.append(" WHERE ").append(key).append("=?");
		super.addParameter(value);
		super.sql = buf;
	}

	/**
	 * 功能:拼装SQL删除语句
	 * 
	 * @param tableName 表名称
	 * @param key       键名
	 * @param val       键值
	 */
	public void createDeleteSql(String tableName, String key, String val) {
		// 检查要操作的数据库表名是否已经指定
		list.clear();
		StringBuilder buf = new StringBuilder();
		buf.append("DELETE FROM ");
		buf.append(tableName);
		buf.append(" WHERE ").append(key).append("=?");
		super.addParameter(val);
		super.sql = buf;
	}

	/**
	 * 功能:拼装SQL删除语句
	 * 
	 * @param data - 返回对象类型
	 */
	public void createDeleteSql(Map<String, Object> data) {
		// 检查要操作的数据库表名是否已经指定
		list.clear();
		this.getTableName(data);
		StringBuilder buf = new StringBuilder();
		buf.append("DELETE FROM ");
		buf.append(tableName);
		String condition = this._createConditionSql(data);
		if (!condition.equals("")) {
			buf.append(" WHERE ");
			buf.append(condition);
		}
		super.sql = buf;
	}

	/**
	 * 功能:拼装查找数据库某表符合条件的记录集合的SQL查询语句
	 * 
	 * <pre>
	 * 1.根据表结构的定义meta，从map集合中找出相关的表字段值作为查询参数
	 * 2.如果字段类型是文本、数字或日期，则根据辅助判断参数拼接SQL条件语句
	 * 3.示例：表名：user
	 * 字段：userName 字符串；age 数字；birthday 日期；
	 * Map<String, Object> map = new HashMap<String, Object>();
	 * map.put("userName", "fgg");
	 * map.put("userName_link", "like");
	 * map.put("age", 20);
	 * map.put("age_link", ">");
	 * map.put("birthday_from", "2013-06-25");
	 * map.put("birthday_to", "2014-06-25");
	 * 结果sql输出：select * from user where userName like ? and age>? and (birthday>=? and birthday<=?)
	 * 查询参数："%fgg%"，20，"2013-06-25"，"2014-06-25"
	 * </pre>
	 * 
	 * @param map - 数据参数
	 * @return 结果SQL文
	 */
	public String _createConditionSql(Map<String, Object> map) {
		if (map.size() == 0) {
			return "";
		}
		Object val = null;
		String lin = null;
		// 获取查询条件参数
		StringBuilder sb = new StringBuilder();
		Map<String, String> link = new HashMap<>();
		// 把比较符和条件分开
		for (String key : map.keySet()) {
			int index = key.indexOf("_link");
			if (index != -1) {
				key = key.substring(0, index);
				link.put(key, map.remove(key).toString());
			}
		}
		// 遍历参数拼接SQL文
		for (String key : map.keySet()) {
			val = map.get(key);
			if (val == null) {
				continue;
			}
			lin = link.get(key);
			int index = key.indexOf("_from");
			if (index != -1) {
				key = key.substring(0, index);
				if (lin == null) {
					lin = ">=";
				}
			}
			index = key.indexOf("_to");
			if (index != -1) {
				key = key.substring(0, index);
				if (lin == null) {
					lin = "<";
				}
			}
			if (lin != null) {
				if (lin.equalsIgnoreCase("in")) {
					String s = val.toString();
					// 如果不是以逗号隔开的数组
					if (s.indexOf(',') == -1) {
						// 使用等值查询
						sb.append(" AND " + key + "=? ");
						list.add(val);
					} else {
						// 使用IN查询
						sb.append(" AND " + key + " IN(");
						for (String str : StringUtils.split(s, ',')) {
							sb.append("?,");
							list.add(str);
						}
						int len = sb.length();
						sb.replace(len - 1, len, ") ");
					}
				} else {
					sb.append(" AND " + key + ' ' + lin + " ? ");
					list.add(val);
				}
			} else {
				sb.append(" AND " + key + "=? ");
				list.add(val);
			}
		}
		// 如果有条件参数
		if (sb.length() > 0) {
			return sb.substring(5);
		} else {
			return "";
		}
	}

	/**
	 * 功能：检查要操作的数据库表名是否已经指定
	 * 
	 * @param map - 参数容器
	 * @return 数据库表定义元数据类
	 */
	public String getTableName(Map<String, Object> map) {
		Object obj = map.remove("tableName");
		if (obj == null) {
			if (tableName == null) {
				throw new RoseRuntimeException("必须要的查询参数：表或视图名必须提供，否则不能拼接SQL文!");
			}
		} else {
			tableName = obj.toString();
		}
		return tableName;
	}

}
